import datetime
import pymysql
import sys
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S')


class baidu_etl_db:

    def __init__(self, date, jine_sum, channel_id, big_channel):

        self.host = "ip"
        self.port = 110
        self.user = "user"
        self.passwd = "pwd"
        self.db = "db"
        # self.yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y%m%d')
        self.yesterday = date
        self.sql = "insert into baidu_channel_cost(date,channel_id,client_id,cost) VALUES (" + self.yesterday + ",'" + channel_id + "','" + big_channel + "'," + jine_sum + ")"
        self.miss_sql = "select channel_id from baidu_channel_cost where client_id='" + big_channel + "' and date ='" + self.yesterday + "'"
        self.sum_sql = "select cost from baidu_channel_cost where client_id='" + big_channel + "' and date ='" + self.yesterday + "'"

    def insert_mysql(self):

        db = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db)
        # 通过cursor创建游标
        cursor = db.cursor()
        try:
            print(self.sql)
            cursor.execute(self.sql)
            # 提交SQL
            db.commit()
            logging.info(self.sql + "---插入成功！")
        except Exception as e:
            print(datetime.datetime.now(), e)
            # 发生错误时回滚
            db.rollback()
            # 关闭数据库连接
        finally:
            db.close()

    def select_mysql(self, exec_sql):

        db = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db)
        # 通过cursor创建游标
        cursor = db.cursor()
        try:
            cursor.execute(exec_sql)
            channel_id_tuple = cursor.fetchall()
            channel_id_list = []
            for single in channel_id_tuple:
                channel_id_list.append(single[0])
            # 提交SQL
            db.commit()
        except Exception as e:
            print(datetime.datetime.now(), e)
            # 发生错误时回滚
            db.rollback()
            # 关闭数据库连接
        finally:
            db.close()
        return channel_id_list

    def exec_miss(self):
        exec_sql = self.miss_sql
        return self.select_mysql(exec_sql)

    def exec_sum(self):
        exec_sql = self.sum_sql
        return sum(list(map(int, self.select_mysql(exec_sql))))


if __name__ == '__main__':
    date = sys.argv[1]
    jine_sum = sys.argv[2]
    channel_id = sys.argv[3]
    big_channel = sys.argv[4]
    #print(baidu_etl_db(date, jine_sum, channel_id, big_channel).exec_miss())
    #print(baidu_etl_db(date, jine_sum, channel_id, big_channel).exec_sum())
    #baidu_etl_db(date, jine_sum, channel_id, big_channel).insert_mysql()

